<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" import="java.util.*" import="java.security.*" errorPage="" %>
<%
		String Password1 = request.getParameter("Password1");

		//Hash password
		//byte[] b = Password1.getBytes("UTF-8");
		//MessageDigest md = MessageDigest.getInstance("MD5");
		//byte[] hashedP = md.digest(b);
		//Password1 = new String(hashedP);

		String mysJDBCDriver = "com.mysql.jdbc.Driver"; 
		String mysURL = "jdbc:mysql://mysql2.cs.stonybrook.edu:3306/jedale"; 
     	String mysUserID = "jedale"; 
    	String mysPassword = "107200855";

			java.sql.Connection conn=null;
			try 
			{
            	Class.forName(mysJDBCDriver).newInstance();
    			java.util.Properties sysprops=System.getProperties();
    			sysprops.put("user",mysUserID);
    			sysprops.put("password",mysPassword);
        
				//connect to the database
            			conn=java.sql.DriverManager.getConnection(mysURL,sysprops);
            			System.out.println("Connected successfully to database using JConnect");
            
            			java.sql.Statement stmt1=conn.createStatement();
           if (request.getParameter("target").trim().equals("createuser"))
           {
	       		String UserName = request.getParameter("Id");
	    		String FirstName = request.getParameter("FirstName");
	    		String LastName = request.getParameter("LastName");
	    		String SSN = request.getParameter("SSN");
	    		String Address = request.getParameter("Address");
	    		String CC = request.getParameter("CreditCard");
	    		String Email = request.getParameter("Email");
	    		String Telephone = request.getParameter("Telephone");
	    		String ZipCode = request.getParameter("ZipCode");
    		
        	   String valueStatement = "VALUES (" + java.lang.Integer.parseInt(SSN) + " ,' " + LastName +" ',' " + FirstName +  " ',' " +Address + " ',' " + ZipCode + " ',' " + Telephone + "','" + Email + "','" + Password1 + "','" + UserName + "');";
				//out.println(valueStatement);			
        	   	stmt1.executeUpdate("INSERT INTO Person (SSN, LastName, FirstName, Address, ZipCode, Telephone, Email,Pass,Username) " + valueStatement);
           
				stmt1.executeUpdate("INSERT INTO Customer(Rating,CreditCardNum,CustomerID) VALUES(0," + CC + "," + java.lang.Integer.parseInt(SSN) + ");");
//				out.print("insert into Student values('"+Id+"','"+Password1+"','"+Name+"','"+request.getParameter("status")+"')");
				session.invalidate();
				stmt1.close();
			}
           else if(request.getParameter("target").trim().equals("edituser"))
           {
       			String UserName = request.getParameter("Id");
    			String FirstName = request.getParameter("FirstName");
    			String LastName = request.getParameter("LastName");
    			String SSN = request.getParameter("SSN");
    			String Address = request.getParameter("Address");
    			String CC = request.getParameter("CreditCard");
    			String Email = request.getParameter("Email");
    			String Telephone = request.getParameter("Telephone");
    			String ZipCode = request.getParameter("ZipCode");
    			
        	   String valueStatement = "LastName='" + LastName +"', FirstName='" + FirstName +  "',Address='" +Address + "',ZipCode='" + ZipCode + "',Telephone='" + Telephone + "',Email='" + Email + "'";
				System.out.println("UPDATE Person SET " + valueStatement + " WHERE SSN=" + java.lang.Integer.parseInt(SSN) + ");");	
				System.out.println("Update Customer SET CreditCardNum= " + CC + " WHERE CustomerID = "+java.lang.Integer.parseInt(SSN)+";");
       	   		stmt1.executeUpdate("UPDATE Person SET " + valueStatement + " WHERE SSN=" + java.lang.Integer.parseInt(SSN) + ";");
				stmt1.executeUpdate("Update Customer SET CreditCardNum= " + CC + " WHERE CustomerID = "+java.lang.Integer.parseInt(SSN)+";");
				stmt1.close();
           }
           else if(request.getParameter("target").trim().equals("createemployee"))
           {
      			String UserName = request.getParameter("Id");
	   			String FirstName = request.getParameter("FirstName");
	   			String LastName = request.getParameter("LastName");
	   			String SSN = request.getParameter("SSN");
	   			String Address = request.getParameter("Address");
	   			String Email = request.getParameter("Email");
	   			String Telephone = request.getParameter("Telephone");
	   			String ZipCode = request.getParameter("ZipCode");
	   			String Rate = request.getParameter("Rate");
	   			String Level = request.getParameter("Level");
   			
        	   	String valueStatement = " VALUES (" + java.lang.Integer.parseInt(SSN) + " ,' " + LastName +" ',' " + FirstName +  " ',' " +Address + " ',' " + ZipCode + " ',' " + Telephone + "','" + Email + "','" + Password1 + "','" + UserName + "');";			
       	   		stmt1.executeUpdate("INSERT INTO Person (SSN, LastName, FirstName, Address, ZipCode, Telephone, Email,Pass,Username)" + valueStatement);
       	   		stmt1.executeUpdate("INSERT INTO Employee (StartDate,HourlyRate,EmpLevel,EmployeeID) VALUES(GETDATE(),"+ java.lang.Float.parseFloat(Rate) +","+Level + "," +SSN + ");");
          		stmt1.close();           		
           }
           else if(request.getParameter("target").trim().equals("editemployee"))
           {
      			String UserName = request.getParameter("Id");
	   			String FirstName = request.getParameter("FirstName");
	   			String LastName = request.getParameter("LastName");
	   			String SSN = request.getParameter("SSN");
	   			String Address = request.getParameter("Address");
	   			String Email = request.getParameter("Email");
	   			String Telephone = request.getParameter("Telephone");
	   			String ZipCode = request.getParameter("ZipCode");
	   			String Rate = request.getParameter("HR");
	   			String Level = request.getParameter("Level");
	   			
	       	  	String valueStatement = "LastName='" + LastName +"', FirstName='" + FirstName +  "',Address='" +Address + "',ZipCode='" + ZipCode + "',Telephone='" + Telephone + "',Email='" + Email + "'";	
	       	  	System.out.println(valueStatement);
	       	 System.out.println("UPDATE Person SET " + valueStatement + " WHERE SSN=" + java.lang.Integer.parseInt(SSN) + ");");
      	   		stmt1.executeUpdate("UPDATE Person SET " + valueStatement + " WHERE SSN=" + java.lang.Integer.parseInt(SSN) + ");");          	
      	   		stmt1.executeUpdate("UPDATE Employee SET " + "HourlyRate=" + Rate+",EmpLevel="+ Level + " WHERE EmployeeID=" + java.lang.Integer.parseInt(SSN) + ");");      
           }
           else if(request.getParameter("target").trim().equals("deleteuser"))
           {
        	   String UserName = request.getParameter("Id");
	   			String SSN = request.getParameter("SSN");
        	   int empLevel = -1;// (java.lang.Integer)session.getValue("empLevel");
				if(session.getValue("empLevel") != null)
					empLevel = (java.lang.Integer)session.getValue("empLevel");
        		if(UserName.equals((String)session.getValue("login")))
        			{
        				stmt1.executeUpdate("DELETE FROM Customer WHERE  Customer.CustomerID = " + java.lang.Integer.parseInt(SSN));
        				stmt1.executeUpdate("DELETE FROM Person WHERE Person.SSN ="  + java.lang.Integer.parseInt(SSN));
        				session.invalidate();
        			}
        		else if(empLevel == 1 || empLevel == 2)
        		{
    				stmt1.executeUpdate("DELETE FROM Customer WHERE  Customer.CustomerID = " + java.lang.Integer.parseInt(SSN));
    				stmt1.executeUpdate("DELETE FROM Person WHERE Person.SSN ="  + java.lang.Integer.parseInt(SSN));        			
        		}
           }
        	else if(request.getParameter("target").trim().equals("deleteemp"))
       	   {
         	   String UserName = request.getParameter("Id");
 	   			String SSN = request.getParameter("SSN");
         	   int empLevel = -1;// (java.lang.Integer)session.getValue("empLevel");
 				if(session.getValue("empLevel") != null)
 					empLevel = (java.lang.Integer)session.getValue("empLevel");
         		if(UserName.equals((String)session.getValue("login")))
         			{
         				stmt1.executeUpdate("DELETE FROM Customer WHERE  Employee.CustomerID = " + java.lang.Integer.parseInt(SSN));
         				stmt1.executeUpdate("DELETE FROM Person WHERE Person.SSN ="  + java.lang.Integer.parseInt(SSN));
         				session.invalidate();
         			}
         		else if(empLevel == 1 || empLevel == 2)
         		{
     				stmt1.executeUpdate("DELETE FROM Customer WHERE  Employee.CustomerID = " + java.lang.Integer.parseInt(SSN));
     				stmt1.executeUpdate("DELETE FROM Person WHERE Person.SSN ="  + java.lang.Integer.parseInt(SSN));        			
         		}
       	   	}
			} catch(Exception e)
			{
				e.printStackTrace();
				out.print(e.toString());
			}
			finally{
			
				try{conn.close();}catch(Exception ee){};
				
			}
			response.sendRedirect("index.jsp");
			
/*
	}
*/
%>